package com.example.testdbsearchform;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class CustomSQLiteHelper extends SQLiteOpenHelper {

	  private static final String DATABASE_NAME = "search_form.db";
	  private static final int DATABASE_VERSION = 15;

	  // Database creation sql statement
	  private static final String CATEGORY_CREATE = 
		"create table Category (" 
		+ " _id integer primary key,"
	    + " name text not null"
		+ " ); ";
	  private static final String PRODUCT_CREATE =
	    "create table Product (" 
	    + " _id integer primary key," 
	    + " date integer not null," 
	    + " name text not null,"
	    + " category integer not null"
	    + " );";
	  
	  public CustomSQLiteHelper(Context context) {
	    super(context, DATABASE_NAME, null, DATABASE_VERSION);
	  }
	  
	  private static CustomSQLiteHelper instance = null;
	  public static CustomSQLiteHelper getInstance(Context context) {
		  if (instance == null) {
			  instance = new CustomSQLiteHelper(context);
		  }
			return instance;
		}

	  public SQLiteDatabase open() throws SQLException {
	    return getWritableDatabase();
	  }

	  @Override
	  public void onCreate(SQLiteDatabase database) {
	    database.execSQL(CATEGORY_CREATE);
	    database.execSQL("insert into Category (_id, name) values (1, 'category 1');");
	    database.execSQL("insert into Category (_id, name) values (2, 'category 2');");
	    database.execSQL("insert into Category (_id, name) values (3, 'category 3');");
	    
	    SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yy");
	    
	    database.execSQL(PRODUCT_CREATE);
	    try {
			long d1 = (sdf.parse("01.01.2013")).getTime();
			Log.d("TIME", Long.toString(d1));
			Calendar cal = Calendar.getInstance();
			cal.set(2013, 1, 1, 0, 0, 0);
			Log.d("TIME CALC", Long.toString(cal.getTimeInMillis()));
			database.execSQL("insert into Product (_id, date, name, category) values (1, "+
		    		d1+", 'product 1 category 1', 1); ");
			
			cal.add(Calendar.DAY_OF_MONTH, 1);
			long d2 = (sdf.parse("02.01.2013")).getTime();
			database.execSQL("insert into Product (_id, date, name, category) values (2, "+
		    		d2+", 'product 2 category 1', 1); ");
			
			long d3 = (sdf.parse("03.01.2013")).getTime();
			database.execSQL("insert into Product (_id, date, name, category) values (3, "+
		    		d3+", 'product 1 category 2', 2); ");
			
			long d4 = (sdf.parse("04.01.2013")).getTime();
			database.execSQL("insert into Product (_id, date, name, category) values (4, "+
		    		d4+", 'product 2 category 2', 2); ");
			
			long d5 = (sdf.parse("05.01.2013")).getTime();
			database.execSQL("insert into Product (_id, date, name, category) values (5, "+
		    		d5+", 'product 3 category 2', 2); ");
			
			long d6 = (sdf.parse("06.01.2013")).getTime();
			database.execSQL("insert into Product (_id, date, name, category) values (6, "+
					d6+", 'product 4 category 2', 2); ");
			
			long d7 = (sdf.parse("07.01.2013")).getTime();
			database.execSQL("insert into Product (_id, date, name, category) values (7, "+
		    		(new Date(2013, 1, 7)).getTime()+", 'product 1 category 3', 3); ");
			
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  }

	  @Override
	  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	    Log.w(CustomSQLiteHelper.class.getName(),
	        "Upgrading database from version " + oldVersion + " to "
	            + newVersion + ", which will destroy all old data");
	    db.execSQL("DROP TABLE IF EXISTS Product");
	    db.execSQL("DROP TABLE IF EXISTS Category");
	    onCreate(db);
	  }
}
